create database MyConfession
use MyConfession

create table Accounts(
[username] [nvarchar](50) NOT NULL primary key,
	[password] [nvarchar](50) NOT NULL,
	[email] [nvarchar](50) NOT NULL,
	[firstname] [nvarchar](50) NULL,
	[lastname] [nvarchar](50) NULL,
	[fbToken] [nvarchar](max) NULL,
)

create table Pages(
	[id] [int] NOT NULL primary key identity,
	[pageName] [nvarchar](50) NULL,
	[pageLink] [nvarchar](50) NULL,
	[username] [nvarchar](50) NOT NULL foreign key references Accounts(username),
	[description] [nvarchar](50) NULL,
	[imageLink] [nvarchar](50) NULL,
	[fbPageId] [nvarchar](50) NOT NULL,
	[isActive] [bit] not null default 1,
	[hashtag] nvarchar(50) NULL,
)

create table UserPage(
	[username] [nvarchar](50) not null foreign key references Accounts(username),
	[pageId] [int] not null foreign key references Pages(id),
	PRIMARY KEY CLUSTERED 
	(
	[username] ASC,
	[pageId] ASC
	)
)

create table Posts(
	[id] [int] NOT NULL primary key identity,
	[pageId] [int] NOT NULL references Pages(id),
	[postContent] [nvarchar](500) NOT NULL,
	[date] [datetime] NULL,
	[status] [nvarchar](10) NOT NULL,
	[fbPostId] [nvarchar](50) NULL
)

IF OBJECT_ID('sp_checkLogin','p') is not null
	drop procedure sp_checkLogin
GO
create procedure sp_checkLogin
@username nvarchar(50),
@password nvarchar(50)
as
begin
	select * from Accounts where username = @username and [password] = @password
end
go

IF OBJECT_ID('sp_getAllPage','p') is not null
	drop procedure sp_getAllPage
GO
create procedure sp_getAllPage
as
begin
	select * from Pages
end
go


IF OBJECT_ID('sp_getAllPost','p') is not null
	drop procedure sp_getAllPost
GO
create procedure sp_getAllPost
as
begin
	select * from Posts
end
go


IF OBJECT_ID('sp_addAccount','p') is not null
	drop procedure sp_addAccount
GO
create procedure sp_addAccount
@username nvarchar(50),
@password nvarchar(50),
@email nvarchar(50),
@firstname nvarchar(50),
@lastname nvarchar(50)
as
begin
 Insert into Accounts (username,[password],email,firstname,lastname)values  (@username,@password,@email,@firstname,@lastname)
 end
go

IF OBJECT_ID('sp_getAllUserPage','p') is not null
	drop procedure sp_getAllUserPage
GO
create procedure sp_getAllUserPage
as
begin
	select * from UserPage up, Pages p where up.pageId = p.id
end
go
IF OBJECT_ID('sp_addPage','p') is not null
	drop procedure sp_addPage
GO
create procedure sp_addPage
@id int output,
@pagename nvarchar(50),
@pagelink nvarchar(50),
@desc nvarchar(50),
@imagelink nvarchar(50),
@fbID nvarchar(50)
as
begin
 Insert into Pages (pageName,pageLink,[description],imageLink, fbPageId, isActive)values 
  (@pagename, @pagelink,@desc,@imagelink,@fbID, 1)
  select @id = scope_identity()
 end
go



IF OBJECT_ID('sp_updatePage','p') is not null
	drop procedure sp_updatePage
GO
create procedure sp_updatePage
@pageId int,
@pagename nvarchar(50),
@pagelink nvarchar(50),
@desc nvarchar(50),
@imagelink nvarchar(50),
@fbID nvarchar(50)
as
begin
 Update Pages  set 
  pageName = @pagename, 
  pageLink = @pagelink,
  [description] = @desc,
  imageLink = @imagelink, 
  fbPageId = @fbID
  where id=@pageId
 end
go

IF OBJECT_ID('sp_updatePageActive','p') is not null
	drop procedure sp_updatePageActive
GO
create procedure sp_updatePageActive
@pageId int,
@isActive bit
as
begin
 Update Pages  set 
  isActive = @isActive
  where id=@pageId
 end
go

IF OBJECT_ID('sp_addPost','p') is not null
	drop procedure sp_addPost
GO
create procedure sp_addPost
@id int output,
@pageId int,
@postContent nvarchar(50),
@date datetime,
@status nvarchar(50)
as
begin
 Insert into Posts (pageId, postContent,[date], [status])values 
  (@pageId, @postContent,@date,@status)
  select @id = scope_identity()
 end
go

IF OBJECT_ID('sp_updatePost','p') is not null
	drop procedure sp_updatePost
GO
create procedure sp_updatePost
@id int,
@content nvarchar(max),
@status nvarchar(50),
@fbPostId nvarchar(50)
as
begin
 Update Posts set postContent=@content, [status]=@status, fbPostId=@fbPostId
 where id=@id
 end
go